package DBMGR;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import Bean.MemberBean;

public class DBMgr {

	private Connection conn = null;

	public DBMgr() {
		DbConnect db = new DbConnect();
		conn = db.getConnection();
	}

	public MemberBean member() {
		MemberBean bean = new MemberBean();
		Statement stmt;
		String sql;
		ResultSet rs;
		sql = "select * from member where id='jenny'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	public MemberBean searchMember(String id) {
		MemberBean bean = new MemberBean();
		String sql;
		ResultSet rs;
		PreparedStatement pstmt;
		sql = "select * from member where id=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	public MemberBean searchMem(String id) {
		MemberBean bean = new MemberBean();
		Statement stmt;
		String sql;
		ResultSet rs;
		sql = "select * from member where id='" + id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	public MemberBean searchName(String name) {
		MemberBean bean = new MemberBean();
		Statement stmt;
		String sql;
		ResultSet rs;
		sql = "select * from member where name='" + name + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				bean.setId(rs.getString(1));
				bean.setPassword(rs.getString(2));
				bean.setName(rs.getString(3));
				bean.setAge(rs.getInt(4));
				bean.setGender(rs.getString(5));
				bean.setEmail(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bean;
	}

	public boolean loginCheck(String id, String pw) {
		boolean flag = false;
		Statement stmt;
		String sql = "select count(*) from member where id = '" + id
				+ "' and password = '" + pw + "'";
		ResultSet rs;

		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			int sel = 0;
			while (rs.next()) {
				sel = rs.getInt(1);
			}
			if (sel > 0) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	public boolean join(MemberBean bean) {
		Statement stmt;
		boolean suc = false;
		String sql = "insert into member values ('" + bean.getId() + "', '"
				+ bean.getPassword() + "', '" + bean.getName() + "', "
				+ bean.getAge() + ", '" + bean.getGender() + "', '"
				+ bean.getEmail() + "');";
		try {
			stmt = conn.createStatement();
			suc = stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return suc;
	}
}
